/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tamnhat.report.model;

import com.tamnhat.report.entity.ReportP1;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
public class ReportP1Model {
    public static ArrayList<ReportP1> getReport(String tuNgay,String denNgay,int doiTac) throws Exception{
		ArrayList<ReportP1> list = new ArrayList<ReportP1>();
		Connection conn = null;
                Statement st = null;
                ResultSet rs = null;
		try{
			conn = AppServer.getConnection();
			st = conn.createStatement();
                        String strSQL = "SELECT   cmdcode, telco_id, COUNT ( * ) sub_count FROM   cms_admin.mo_queue WHERE send_date >= to_date('"+tuNgay+"','dd/MM/yyyy') AND send_date < to_date('"+denNgay+"','dd/MM/yyyy') + 1 AND telco_id IS NOT NULL AND cmdcode IN ('SOI', 'XS') AND (second_partner_id = "+doiTac+") GROUP BY ROLLUP (cmdcode,telco_id)";
			//ResultSet rs = st.executeQuery("SELECT cmdcode, COUNT ( * ) sub_count FROM   cms_admin.mo_queue WHERE send_date >= to_date('"+tuNgay+"','dd/MM/yyyy') AND send_date < to_date('"+denNgay+"','dd/MM/yyyy') + 1 AND telco_id IS NOT NULL AND cmdcode IN ('SOI', 'XS') AND (second_partner_id = 2 OR '1' IS NULL) GROUP BY   ROLLUP (cmdcode)");
			rs = st.executeQuery(strSQL);
			while(rs.next()){
				ReportP1 rp = new ReportP1();				
				if(rs.getString(1) == null){
					rp.setTongcong(Integer.parseInt(rs.getString(3)));					
				}
				else{
					rp.setMaDV(rs.getString(1));
					if(rs.getString(2) == null ) rp.setMang("");
					else rp.setMang(rs.getString(2));
					rp.setSoluong(Integer.parseInt(rs.getString(3)));
				}
				list.add(rp);
			}
			return list;
                } finally{
			Database.closeObject(rs);
			Database.closeObject(st);
                        Database.closeObject(conn);
                }
	}
}
